﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Lima.ResumeSys.Class
{
    public class clsDbOperate
    {
        private OleDbDataReader ole_reader = null;
        private DataTable dt = null;

        static string exePath = GetApplicationRoot();//数据库在本程序中所在路径
        static string Psd = "LM8800";
        OleDbConnection conn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=" + exePath + "SystemDate.mdb;" + "Jet OLEDB:Database Password=" + Psd);
        OleDbCommand oleCommand = new OleDbCommand();//操作查询
                                                     // 获取应用程序根目录
        public static string GetApplicationRoot()
        {
            // 优先使用BaseDirectory，因为它更可靠
            string root = AppDomain.CurrentDomain.BaseDirectory;

            // 如果是调试环境，可能需要特殊处理
            if (Debugger.IsAttached && root.Contains("bin\\Debug"))
            {
                // 向上移动两级到项目目录
                root = Path.GetFullPath(Path.Combine(root, @"..\..\"));
            }

            return root + @"Data\";
        }
        /// <summary>
        /// 输入sql命令，得到DataTable对象
        /// </summary>
        /// <param name="sql">sql命令</param>
        /// <returns>数据列表</returns>
        public DataTable dtSelectResult(string sql)
        {
            DataTable dt = new DataTable(); //新建表对象
            try
            {
                conn.Open();
                OleDbDataAdapter da = new OleDbDataAdapter(sql, conn); //创建适配对象
                da.Fill(dt); //用适配对象填充表对象
                conn.Close();
            }
            catch (Exception)
            {
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }
        /// <summary>
        /// 输入sql命令，检查数据表中是否有该数据信息
        /// </summary>
        /// <param name="sql">sql命令</param>
        /// <returns></returns>
        public int intSelectResultCount(string sql)
        {
            DataSet ds = new DataSet(); //新建表对象
            int n = 0;
            try
            {
                conn.Open();
                OleDbDataAdapter da = new OleDbDataAdapter(sql, conn); //创建适配对象

                da.Fill(ds); //用适配对象填充表对象
                n = ds.Tables[0].Rows.Count;
            }
            catch (Exception)
            {
            }
            finally
            {
                conn.Close();
            }
            return n;
        }


        /// <summary>
        /// 执行不带参数的SQL语句（增删改）
        /// </summary>
        /// <param name="sql">SQL命令</param>
        /// <returns>语句执行成功的条数</returns>
        public int Del_Ins_Upd_Result(string sql)
        {
            int n = 0;
            try
            {
                conn.Open();
                OleDbCommand comm = new OleDbCommand(sql, conn);
                n = comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            return n;
        }

        /// <summary>
        /// 执行带参数的SQL语句（增删改）
        /// </summary>
        /// <param name="sql">SQL命令</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>语句执行成功的条数</returns>
        public int ExecuteNonQuery(string sql, OleDbParameter[] parameters)
        {
            int n = 0;
            try
            {
                conn.Open();
                OleDbCommand comm = new OleDbCommand(sql, conn);

                // 添加参数
                if (parameters != null && parameters.Length > 0)
                {
                    comm.Parameters.AddRange(parameters);
                }

                // 执行非查询操作（INSERT/UPDATE/DELETE）
                n = comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            return n;
        }

        /// <summary>
        /// 执行查询并返回DataTable
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>包含结果的DataTable</returns>
        public DataTable ExecuteQuery(string sql, OleDbParameter[] parameters = null)
        {
            DataTable dt = new DataTable();
            try
            {
                conn.Open();
                // 创建 DataAdapter
                OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);

                // 添加参数到 DataAdapter 的 SelectCommand
                if (parameters != null && parameters.Length > 0)
                {
                    foreach (OleDbParameter param in parameters)
                    {
                        da.SelectCommand.Parameters.AddWithValue(param.ParameterName,param.Value);
                    }
                    //da.SelectCommand.Parameters.AddRange(parameters);
                }
                da.Fill(dt);  // 直接使用da填充DataTable
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            return dt;
        }

        /// <summary>
        /// 执行查询并返回第一行第一列的值
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>第一行第一列的值</returns>
        public object ExecuteScalar(string sql, OleDbParameter[] parameters = null)
        {
            object result = null;
            try
            {
                conn.Open();
                OleDbCommand comm = new OleDbCommand(sql, conn);

                if (parameters != null && parameters.Length > 0)
                {
                    foreach (OleDbParameter param in parameters)
                    {
                        comm.Parameters.AddWithValue(param.ParameterName, param.Value);
                    }
                   // comm.Parameters.AddRange(parameters);
                }

                result = comm.ExecuteScalar();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            return result;
        }
        /// <summary>
        /// 从数据库里面获取数据
        /// </summary>
        ///<param name="strSql">查询语句</param>
        /// <returns>数据列表</returns>
        public DataTable GetDataTableFromDB(string strSql)
        {
            if (exePath == null)
            {
                return null;
            }
            try
            {
                conn.Open();//打开连接
                if (conn.State == ConnectionState.Closed)
                {
                    return null;
                }
                oleCommand.CommandText = strSql;
                oleCommand.Connection = conn;
                ole_reader = oleCommand.ExecuteReader(CommandBehavior.Default);
                dt = ConvertOleDbReaderToDataTable(ref ole_reader);
                ole_reader.Close();
                ole_reader.Dispose();
            }
            catch (System.Exception e)
            {
                //Console.WriteLine(e.ToString());
                MessageBox.Show(e.Message);
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
            return dt;
        }
        /// <summary>
        /// 转换数据格式
        /// </summary>
        ///<param name="reader">数据源</param>
        /// <returns>数据列表</returns>
        private DataTable ConvertOleDbReaderToDataTable(ref OleDbDataReader reader)
        {
            DataTable dtTab = null;
            DataRow dr = null;
            int dataColumnCount = 0;
            int i = 0;
            dataColumnCount = reader.FieldCount;
            dtTab = BuildAndInitDataTable(dataColumnCount);
            if (dtTab == null)
            {
                return null;
            }
            while (reader.Read())
            {
                dr = dtTab.NewRow();
                for (i = 0; i < dataColumnCount; ++i)
                {
                    dr[i] = reader[i];
                }
                dtTab.Rows.Add(dr);
            }
            return dtTab;
        }
        /// <summary>
        /// 创建并初始化数据列表
        /// </summary>
        ///<param name="fieldCount">列的个数</param>
        /// <returns>数据列表</returns>
        private DataTable BuildAndInitDataTable(int fieldCount)
        {
            DataTable dtTab = null;
            DataColumn dc = null;
            int i = 0;
            if (fieldCount <= 0)
            {
                return null;
            }
            dtTab = new DataTable();
            for (i = 0; i < fieldCount; ++i)
            {
                dc = new DataColumn(i.ToString());
                dtTab.Columns.Add(dc);
            }
            return dtTab;
        }
    }
}
